﻿using Spire.Xls;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Dotnet.Code
{
    public class SpireExcel:ExcelDocument
    {   
        public SpireExcel(string fileName) : base(fileName) { }

        private Workbook LoadExcelFile(string password="")
        {
            Workbook workbook = new Workbook();
            if (string.IsNullOrEmpty(password) == false)
            {
                workbook.OpenPassword = password;//文档密码
            }
            workbook.LoadFromFile(FileName);//加载excel文档
            if (workbook.Worksheets.Count == 0)
                return null;
            return workbook;
            //Worksheet sheet = workbook.Worksheets[0];
        }

        public override DataTable ExcelToDataTable(int sheet=0,string password = "")
        {
            DataTable dt = new DataTable();
            try
            {
                Workbook workbook = LoadExcelFile(password);
                Worksheet wbSheet = workbook.Worksheets[sheet];
                //表头
                CellRange header = wbSheet.Rows[0];
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastColumn; i++)
                {
                    string colName = header.Cells[i].DisplayedText.Trim();
                    if (string.IsNullOrEmpty(colName) == true)
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    else
                        dt.Columns.Add(new DataColumn(colName.ToString()));
                    columns.Add(i);
                }
                //数据
                int rows = wbSheet.LastRow - 1;
                for (int i = wbSheet.FirstRow; i <= rows; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        CellRange cell = wbSheet.Rows[i].Cells[j];
                        bool isFormulaDateTimeExce = true;
                        string cellValue = "";
                        try
                        {
                            cellValue = cell.DateTimeValue.ToString("yyyy-MM-dd HH:mm:ss");
                            if (cellValue.StartsWith("0001")) isFormulaDateTimeExce = false;
                        }
                        catch { isFormulaDateTimeExce = false; }

                        if (isFormulaDateTimeExce == false) //非时间串
                        {
                            cellValue = cell.DisplayedText.Trim();
                        }
                        dr[j] = cellValue;
                        if (string.IsNullOrEmpty(cellValue) == false)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                    OnExcelToTable(new ExcelToTableArgs() { RowsCount = rows,Index = i,CurrDataRow = dr,CurrDataTable = dt });
                }
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public DataTable ExcelToDataTable(string password = "")
        {
            return ExcelToDataTable(0, password);
        }
    }
}
